PARKING SYSTEM CASE STUDY

Step 2: Entity Identification (Parking Lot)

Parking System Banner

The parking lot management operational workflow is represented by 6 core tables:

🔹 PARKING_LOTS — Garages/Structures

Attribute	Data Type	Description
lot_id	INT, PK	Unique parking lot identifier
name	VARCHAR	Name of the parking structure (e.g. "Terminal A Multi-level")
address	VARCHAR	Physical address location
total_capacity	INT	Aggregate capacity across all floors

🔹 PARKING_FLOORS — Levels within Lot

Attribute	Data Type	Description
floor_id	INT, PK	Unique floor level identifier
lot_id	INT, FK	Associated parking structure
floor_number	INT	Floor/level indicator (e.g. 1, 2, -1 for basement)
capacity	INT	Max spots allocated to this level

🔹 PARKING_SPOTS — Spaces

Attribute	Data Type	Description
spot_id	INT, PK	Unique physical spot identifier
floor_id	INT, FK	Level containing this spot
spot_number	VARCHAR	Alphanumeric spot sign (e.g. "A-102")
spot_type	VARCHAR	Classification (`HANDICAPPED`, `ELECTRIC_VEHICLE`, `COMPACT`, `MOTORCYCLE`, `LARGE`)
status	VARCHAR	Real-time occupancy status (`AVAILABLE`, `OCCUPIED`)

🔹 TICKETS — Parking Session Records

Attribute	Data Type	Description
ticket_id	INT, PK	Unique session barcode identifier
spot_id	INT, FK	Reserved/occupied space
license_plate	VARCHAR	Vehicle license plate number
entry_time	TIMESTAMP	Ticket generation epoch
exit_time	TIMESTAMP, NULLABLE	Exit terminal checkout epoch
amount_charged	DECIMAL, NULLABLE	Invoice billing amount
status	VARCHAR	Ticket lifecycle state (`ACTIVE`, `PAID`, `EXPIRED`)

🔹 PAYMENTS — Financial Log

Attribute	Data Type	Description
payment_id	INT, PK	Unique transactional billing record
ticket_id	INT, FK	Associated invoice ticket reference
transaction_ref	VARCHAR, UNIQUE	Credit Card processor validation key
amount	DECIMAL	Charged financial total
payment_method	VARCHAR	Processor method (`CREDIT_CARD`, `CASH_KIOSK`, `MOBILE_PAY`)
payment_time	TIMESTAMP	Completed transaction epoch

🔹 PARKING_RATES — Dynamic Pricing Slate

Attribute	Data Type	Description
rate_id	INT, PK	Unique rate configuration identifier
lot_id	INT, FK	Associated structure utilizing this pricing rule
spot_type	VARCHAR	Spot multiplier (e.g., premium charge for EV slots)
hourly_rate	DECIMAL	Hourly monetary base charge
weekend_multiplier	DECIMAL	Surge multiplier coefficient

lock

This content is reserved for Premium Members.

Submit Technical Query